﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using NHibernate;
using ParaEditor.Common;
using ParaEditor.Common.NHibernate;
using ParaEditor.Domain.Entities;

namespace ParaEditor.DAO
{
    public class BaseDAO
    {
        private Para3007 p3007;
        private Para3006 p3006;
        private Para4002 p4002;
        private Para4004 p4004;
        private ParameterType pType;
        private VersionContrast versionContrast;
        private object obj;
        private GlobelValue globelValue = null;
        protected static ISession Session { get; set; }
        private NHibernateMember instance = NHibernateMember.GetInstance();

        public BaseDAO(ISession _session, object _obj)
        {
            Session = _session;
            obj = _obj;
            globelValue = new GlobelValue();
        }

        public BaseDAO(object _obj)
        {
            p3007 = new Para3007();
            p3006 = new Para3006();
            p4002 = new Para4002();
            p4004 = new Para4004();
            pType = new ParameterType();
            versionContrast = new VersionContrast();
            Session = instance.GetSession();
            obj = _obj;
            globelValue = new GlobelValue();
        }

        /// <summary>
        /// 根据ID查询对象
        /// </summary>
        /// <param name="objId">ID</param>
        /// <returns>返回结果携带了返回的对象Obj,所抛出的异常Msg</returns>
        public ResultValue GetObjectById(int objId)
        {
            ResultValue rValue = new ResultValue();
            try
            {
                object obj = Session.Get<object>(objId);
                rValue.Obj = obj;
                rValue.Code = 0;
            }
            catch (Exception e)
            {
                rValue.Code = Int32.Parse(ErrorFlag.GET_BY_ID.Split(':')[0]);
                rValue.Msg = e.Message;
            }
            return rValue;
        }

        /// <summary>
        /// Save对象
        /// </summary>
        /// <param name="obj"></param>
        /// <returns>返回结果携带了返回的对象Obj,所抛出的异常Msg</returns>
        public ResultValue CreateObject(object obj)
        {
            ResultValue rValue = new ResultValue();
            try
            {
                Session.Merge(obj);
                //Session.Save(obj);
                Session.Flush();
                //Session.Evict(obj);
                rValue.Code = 0;
                Session.Clear();
            }
            catch (Exception e)
            {
                Session.Clear();
                rValue.Code = Int32.Parse(ErrorFlag.SAVE_ERROR.Split(':')[0]);
                rValue.Msg = e.Message;
            }
            return rValue;
        }

        public bool IsExsit(short infoVer)
        {
            string value = (string)globelValue.TablePrimary[this.obj.GetType()];
            string[] tableAndId = value.Split(':');
            string sqlText = "select count(*) as countNum from " + tableAndId[0] + " where " + tableAndId[1] + " = " + infoVer;
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("countNum", NHibernateUtil.Int32);
            int count = Convert.ToInt32(query.UniqueResult());
            if (count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string GetBenfID(string lineID)
        {
            object result = "0000";
            string sqlText = "select nvl(benf_id,'') as benf_id from TBL_METRO_BENF_INFO where line_id = '" + lineID + "' and benf_ver = (select max(benf_ver) from TBL_METRO_BENF_INFO where line_id = '" + lineID + "')";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("benf_id", NHibernateUtil.String);
            result = query.UniqueResult();
            if (result == null)
            {
                return "0000";
            }
            else
            {
                return query.UniqueResult().ToString();
            }
        }

        public bool IsExsit(short infoVer, int recordid)
        {
            string value = (string)globelValue.TablePrimary[this.obj.GetType()];
            string[] tableAndId = value.Split(':');
            string sqlText = "select count(*) as countNum from " + tableAndId[0] + " where " + tableAndId[1] + " = " + infoVer + " and record_id = " + recordid;
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("countNum", NHibernateUtil.Int32);
            int count = Convert.ToInt32(query.UniqueResult());
            if (count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int GetPointByCnName(string name)
        {
            int result = 0;
            string sqlText = "select mov_amt as point from TBL_METRO_LOCAL_RES where MOV_DETAIL = '" + name + "' and info_ver = (select max(info_ver) from TBL_METRO_LOCAL_RES)";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("point", NHibernateUtil.Int32);
            result = Convert.ToInt32(query.UniqueResult());
            return result;
        }

        public IList<string> GetAllNameCN()
        {
            string sqlQuery = "";
            sqlQuery += " select NAME_CN";
            sqlQuery += "   from (select distinct trim(line_nm_cn) as NAME_CN";
            sqlQuery += "           from tbl_metro_line_info";
            sqlQuery += "          where info_ver = (select parameter_num";
            sqlQuery += "                              from tbl_metro_version_contrast";
            sqlQuery += "                             where version_num =";
            sqlQuery += "                                   (select max(version_num)";
            sqlQuery += "                                      from tbl_metro_version_contrast)";
            sqlQuery += "                               and parameter_id = 'LINEINFO')";
            sqlQuery += "          order by NAME_CN)";
            sqlQuery += " union all";
            sqlQuery += " select NAME_CN";
            sqlQuery += "   from (select distinct trim(Station_nm_cn) as NAME_CN";
            sqlQuery += "           from tbl_metro_station_info";
            sqlQuery += "          where station_ver =";
            sqlQuery += "                (select parameter_num";
            sqlQuery += "                   from tbl_metro_version_contrast";
            sqlQuery += "                  where version_num =";
            sqlQuery += "                        (select max(version_num)";
            sqlQuery += "                           from tbl_metro_version_contrast)";
            sqlQuery += "                    and parameter_id = 'STATINFO')";
            sqlQuery += "          order by NAME_CN)";
            sqlQuery += " union all";
            sqlQuery += " select NAME_CN";
            sqlQuery += "   from (SELECT distinct trim(NAME_CN) NAME_CN";
            sqlQuery += "           FROM TBL_METRO_TICKET_TYPE";
            sqlQuery += "          WHERE INFO_VER = (select parameter_num";
            sqlQuery += "                              from tbl_metro_version_contrast";
            sqlQuery += "                             where version_num =";
            sqlQuery += "                                   (select max(version_num)";
            sqlQuery += "                                      from tbl_metro_version_contrast)";
            sqlQuery += "                               and parameter_id = 'TICKTYPE')";
            sqlQuery += "          order by NAME_CN)";
            ISQLQuery query = Session.CreateSQLQuery(sqlQuery).AddScalar("NAME_CN", NHibernateUtil.String);
            return query.List<string>();

        }

        public IList GetObjectByInfoVer(int infoVer)
        {

            IList iList = null;
            try
            {
                ObservableCollection<object> list = new ObservableCollection<object>();
                string value = (string)globelValue.TablePrimary[this.obj.GetType()];
                string[] tableAndId = value.Split(':');
                ISQLQuery query = Session.CreateSQLQuery("select * from " + tableAndId[0] + " where " + tableAndId[1] + " = " + infoVer + "").AddEntity(this.obj.GetType());
                iList = query.List();
            }
            catch (Exception e)
            {
            }

            return iList;
        }



        public IList GetAll()
        {
            IList objList = null;
            try
            {
                objList = Session.CreateCriteria(
                       this.obj.GetType()).List();
            }
            catch (Exception e)
            {

            }
            return objList;
        }

        public ObservableCollection<object> GetAllByType()
        {
            ObservableCollection<object> list = new ObservableCollection<object>();
            IList objList = null;
            try
            {
                objList = GetAll();
                foreach (var item in objList)
                {
                    list.Add(item);
                }
            }
            catch (Exception e)
            {

            }
            return list;
        }

        /// <summary>
        /// 查询当前参数的最大版本号
        /// </summary>
        /// <returns></returns>
        public int GetMaxID()
        {
            string value = (string)globelValue.TablePrimary[this.obj.GetType()];
            string[] tableAndId = value.Split(':');
            string sqlText = "select nvl(max(" + tableAndId[1] + "),0) as maxid from " + tableAndId[0];
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("maxid", NHibernateUtil.Int32);
            int maxid = Convert.ToInt32(query.UniqueResult());
            return maxid;
        }

        public int GetMaxOffset(int infoVer)
        {
            int maxid = 0;
            string value = (string)globelValue.TablePrimary[this.obj.GetType()];
            string[] tableAndId = value.Split(':');
            string sqlText = "select max(MOV_AMT) as maxid from TBL_METRO_LOCAL_RES where INFO_VER = " + infoVer;
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("maxid", NHibernateUtil.Int32);
            maxid = Convert.ToInt32(query.UniqueResult());
            return maxid;
        }

        public int GetMaxID(string issueTrade)
        {
            string value = (string)globelValue.TableParamID[issueTrade];
            string[] tableAndId = value.Split(':');
            string sqlText = "select nvl(max(" + tableAndId[1] + "),0) as maxid from " + tableAndId[0];
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("maxid", NHibernateUtil.Int32);
            int maxid = Convert.ToInt32(query.UniqueResult());
            return maxid;
        }

        public int GetMaxBenfVer()
        {
            string sqlText = "select nvl(max(benf_ver),0) as maxid from tbl_metro_benf_info";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("maxid", NHibernateUtil.Int32);
            int maxid = Convert.ToInt32(query.UniqueResult());
            return maxid;
        }

        public void UpdateColumn(string columnName, int val, short infoVer, short recordid)
        {
            string value = (string)globelValue.TablePrimary[this.obj.GetType()];
            string[] tableAndId = value.Split(':');
            string sqlText = " update " + tableAndId[0] + " set " + columnName + " = " + val + " where record_id = " + recordid + " and " + tableAndId[1] + " = " + infoVer;
            ISQLQuery Query = Session.CreateSQLQuery(sqlText).AddEntity(this.obj.GetType());
            Query.ExecuteUpdate();
            Session.Flush();
            Session.Clear();
        }

        public ObservableCollection<int> GetAllVersion()
        {
            ObservableCollection<int> ocList = new ObservableCollection<int>();
            try
            {
                string value = (string)globelValue.TablePrimary[this.obj.GetType()];
                string[] tableAndId = value.Split(':');
                string sqlText = "select distinct " + tableAndId[1] + " as infover from " + tableAndId[0] + " order by " + tableAndId[1] + " desc";
                ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("infover", NHibernateUtil.Int32);
                IList<int> list = query.List<Int32>();
                foreach (int item in list)
                {
                    ocList.Add(item);
                }
            }
            catch (Exception e)
            { 
            }
            return ocList;
        }

        public ObservableCollection<Record> GetLastestFeiGaoFengRecordID()
        {
            ObservableCollection<Record> ocList = new ObservableCollection<Record>();
            try
            {
                string value = (string)globelValue.TablePrimary[this.obj.GetType()];
                string[] tableAndId = value.Split(':');
                string sqlText = "select record_id from TBL_METRO_TIME_NON_BUSY where info_ver = (select max(info_ver) from TBL_METRO_TIME_NON_BUSY) order by record_id desc";
                ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("record_id", NHibernateUtil.Int32);
                IList<int> list = query.List<Int32>();
                Record obj;
                foreach (int item in list)
                {
                    obj = new Record();
                    obj.RecordID = Int16.Parse(item.ToString());
                    ocList.Add(obj);
                }
            }
            catch (Exception e)
            {
            }
            return ocList;
        }

        public ObservableCollection<Record> GetLastestDaZheRecordID()
        {
            ObservableCollection<Record> ocList = new ObservableCollection<Record>();
            try
            {
                string value = (string)globelValue.TablePrimary[this.obj.GetType()];
                string[] tableAndId = value.Split(':');
                string sqlText = "select record_id from TBL_METRO_PRICE_RATE where info_ver = (select max(info_ver) from TBL_METRO_PRICE_RATE) order by record_id desc";
                ISQLQuery query = Session.CreateSQLQuery(sqlText).AddScalar("record_id", NHibernateUtil.Int32);
                IList<int> list = query.List<Int32>();
                Record obj;
                foreach (int item in list)
                {
                    obj = new Record();
                    obj.RecordID = Int16.Parse(item.ToString());
                    ocList.Add(obj);
                }
            }
            catch (Exception e)
            {
            }
            return ocList;
        }

        public ResultValue DeleteObject(object obj)
        {
            ResultValue rValue = new ResultValue();
            try
            {
                Session.Delete(obj);
                
                rValue.Code = 0;
            }
            catch (Exception e)
            {
                rValue.Code = Int32.Parse(ErrorFlag.SAVE_ERROR.Split(':')[0]);
                rValue.Msg = e.Message;
            }
            finally
            {
                Session.Flush();
                Session.Clear();
            }
            return rValue;
        }

        public ResultValue DeleteObject(object obj, int infoVer)
        {
            Hashtable ParamTypeTable = this.GetAllParamType();
            string paramID = (string)globelValue.TableIssueTrade[this.obj.GetType()];
            ResultValue rv = new ResultValue();

            string querySQL = "select count(*) rec_num from VIW_METRO_INNER_VER_CTRL where version_num = " + infoVer + " and version_content = (select parameter_id from tbl_metro_parameter_type where issue_trade = '" + paramID + "')";
            ISQLQuery query = Session.CreateSQLQuery(querySQL).AddScalar("rec_num", NHibernateUtil.Int32);
            int recNum = Convert.ToInt32(query.UniqueResult());
            if (recNum == 0)
            {
                rv = this.DeleteObject(obj);
            }
            else
            {
                rv.Code = 1000;
                rv.Msg = "此参数当前有效，无法删除！";
            }
            return rv;
        }

        /// <summary>
        /// 获取当前生效的线路
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<CurrentLine> GetCurrentLine()
        {

            ObservableCollection<CurrentLine> currentLines = new ObservableCollection<CurrentLine>();
            string sqlText = "select t.* from tbl_metro_line_info t where info_ver = (select max(info_ver) from tbl_metro_line_info)";
            //string sqlText = "select t.* from tbl_metro_line_info t where info_ver =  (select parameter_num from tbl_metro_version_contrast where version_num = (select max(version_num) from tbl_metro_version_contrast ) and parameter_id = 'LINEINFO')";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(p3007.GetType());
            //ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity("Para3007");
            IList<Para3007> list = query.List<Para3007>();
            CurrentLine currentLine = null;
            foreach (Para3007 item in list)
            {
                currentLine = new CurrentLine();
                currentLine.LineID = item.para3007Pk.LineID;
                currentLine.LineNameCn = item.LineNmCn;
                currentLine.LineNameEn = item.LineNmEn;
                currentLines.Add(currentLine);
            }
            return currentLines;
        }

        /// <summary>
        /// 获取当前生效的车站
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<CurrentStation> GetCurrentStation(string line)
        {
            ObservableCollection<CurrentStation> currentStations = new ObservableCollection<CurrentStation>();
            string sqlText = "select t.* from tbl_metro_station_info t where line_id = '" + line + "'  and station_ver =  (select max(station_ver) from tbl_metro_station_info t)";
            //string sqlText = "select t.* from tbl_metro_station_info t where line_id = '" + line + "'  and station_ver =  (select parameter_num from tbl_metro_version_contrast where version_num = (select max(version_num) from tbl_metro_version_contrast ) and parameter_id = 'STATINFO')";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(p3006.GetType());
            IList<Para3006> list = query.List<Para3006>();
            CurrentStation currentStation = null;
            foreach (Para3006 item in list)
            {
                currentStation = new CurrentStation();
                currentStation.LineID = item.LineId;
                currentStation.StationID = item.para3006Pk.StationNid.Substring(0,4);
                currentStation.StationNameCn = item.StationNmCn;
                currentStation.StationNameEn = item.StationNmEn;
                currentStations.Add(currentStation);
            }
            if (line.Equals("00"))
            {
                CurrentStation defaultStation = new CurrentStation();
                defaultStation.LineID = "00";
                defaultStation.StationID = "0000";
                defaultStation.StationNameCn = "未使用";
                currentStations.Add(defaultStation);
            }
            return currentStations;
        }

        public Hashtable GetAllParamType()
        {
            Hashtable paramTypes = new Hashtable();
            string sqlText = "select * from TBL_METRO_PARAMETER_TYPE t where trim(parameter_id) not in('PTBLAC','PTBLACI') and issue_trade is not null";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(pType.GetType());
            IList<ParameterType> list = query.List<ParameterType>();
            foreach (ParameterType item in list)
            {
                paramTypes.Add(item.issueTrade, item.parameterId);
            }
            return paramTypes;
        }

        public Hashtable GetAllParamTypeDetail()
        {
            Hashtable paramTypes = new Hashtable();
            string sqlText = "select * from TBL_METRO_PARAMETER_TYPE t where trim(parameter_id) not in('PTBLAC','PTBLACI') and issue_trade is not null";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(pType.GetType());
            IList<ParameterType> list = query.List<ParameterType>();
            foreach (ParameterType item in list)
            {
                paramTypes.Add(item.parameterId, item.parameterDescribe);
            }
            return paramTypes;
        }

        public ObservableCollection<Para4004> GetParam4004()
        {

            ObservableCollection<Para4004> currenParams = new ObservableCollection<Para4004>();
            /*
            string sqlText = "select t.* from TBL_METRO_STATION_AREA t where info_Ver = (select max(info_Ver) from TBL_METRO_STATION_AREA ) ";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(p4004.GetType());
            IList<Para4004> list = query.List<Para4004>();
            foreach (Para4004 item in list)
            {
                currenParams.Add(item);
            }
             */
            Para4004 p4004 = new Para4004();
            Para4004Pk p4004Pk = new Para4004Pk();
            p4004Pk.infoVer = 0;
            p4004Pk.areaId = 0;
            p4004.para4004Pk = p4004Pk;
            p4004.areaInfo = 0;
            currenParams.Add(p4004);
            return currenParams;
        }

        public ObservableCollection<VersionContrast> GetVersionContrast()
        {
            ObservableCollection<VersionContrast> currenParams = new ObservableCollection<VersionContrast>();
            string sqlText = "select t.* from TBL_METRO_VERSION_CONTRAST t where version_Num = (select max(version_Num) from TBL_METRO_VERSION_CONTRAST ) ";
            ISQLQuery query = Session.CreateSQLQuery(sqlText).AddEntity(versionContrast.GetType());
            IList<VersionContrast> list = query.List<VersionContrast>();
            foreach (VersionContrast item in list)
            {

                currenParams.Add(item);
            }
            return currenParams;
        }

        public void DeleteBenfInfo()
        {
            BenfInfo obj = new BenfInfo();
            string sqlText = " delete from TBL_METRO_BENF_INFO";
            ISQLQuery Query = Session.CreateSQLQuery(sqlText).AddEntity(obj.GetType());
            Query.ExecuteUpdate();
            Session.Flush();
            Session.Clear();
        }

        public ResultValue BatchCreateObject(ObservableCollection<object> objs, int infoVer)
        {
            ResultValue rValue = new ResultValue();
            try
            {
                foreach (var obj in objs)
                {
                    Session.SaveOrUpdate(obj);
                }
                Session.Flush();
                Session.Clear();
                rValue.Code = 0;
            }
            catch (Exception e)
            {
                rValue.Code = Int32.Parse(ErrorFlag.SAVE_ERROR.Split(':')[0]);
                rValue.Msg = e.Message;
            }
            return rValue;
        }



        public ResultValue FlushSession()
        {
            ResultValue rValue = new ResultValue();
            try
            {
                Session.Flush();
                Session.Clear();
                rValue.Code = 0;
            }
            catch(Exception e)
            {
                rValue.Code = Int32.Parse(ErrorFlag.SAVE_ERROR.Split(':')[0]);
                rValue.Msg = e.Message;
            }
            return rValue;
        }

        public ResultValue BatchCreateObjectThread(IList<Para4030> objs)
        {
            ResultValue rValue = new ResultValue();
            //任务拆分
            int count = objs.Count;
            int bodyCount = count / 4;
            int tailCount = count - bodyCount * 4;
            int offset = 0;
            IList<Para4030> body1 = new List<Para4030>();
            IList<Para4030> body2 = new List<Para4030>();
            IList<Para4030> body3 = new List<Para4030>();
            IList<Para4030> body4 = new List<Para4030>();
            IList<Para4030> tail = new List<Para4030>();
            for (int i = 0; i < count; i++)
            { 

            }
            return rValue;
        }

        public ObservableCollection<TicketType> GetAllTicketType()
        {
            ObservableCollection<TicketType> TicketCollection = new ObservableCollection<TicketType>();
            string sql = " select * ";
            sql += "   from TBL_METRO_TICKET_TYPE where info_ver = (select max(info_ver) from TBL_METRO_TICKET_TYPE)";
            ISQLQuery query = Session.CreateSQLQuery(sql).AddEntity(p4002.GetType());
            IList<Para4002> list = query.List<Para4002>();
            TicketType ticketType = null;
            foreach (Para4002 item in list)
            {
                ticketType = new TicketType();
                ticketType.TicketID = item.para4002Pk.ticketType;
                ticketType.TicketName = item.nameCn;
                TicketCollection.Add(ticketType);
            }
            return TicketCollection;
        }
    }
}
